
package com.htj.erp.service;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.annotation.Resource;

import org.apache.commons.lang3.exception.ExceptionUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.htj.erp.dao.OrderbDao;
import com.htj.erp.dao.OrderhDao;
import com.htj.erp.dao.ProductDao;
import com.htj.erp.dao.mybatis.OrderbMapper;
import com.htj.erp.dao.mybatis.OrderhMapper;
import com.htj.erp.exception.BusinessException;
import com.htj.erp.model.jpa.HRecvAddress;
import com.htj.erp.model.jpa.Orderb;
import com.htj.erp.model.jpa.Orderh;
import com.htj.erp.model.jpa.Product;
import com.htj.erp.model.mybatis.HRecvAddressPO;
import com.htj.erp.model.mybatis.OrderbPO;
import com.htj.erp.model.mybatis.OrderhPO;
import com.htj.erp.util.ExcelUtil;
import com.htj.erp.vo.OrderbVO;
import com.htj.erp.vo.OrderhVO;
import com.kedacom.core.utils.ApplicationUtil;
import com.kedacom.security.service.GenericCpsService;
import com.kedacom.utils.BeanUtils;
import com.kedacom.utils.DateFormatUtil;
import com.kedacom.utils.JSONUtil;
import com.kedacom.utils.StringUtil;
/**
 * 
 *
 * 
 * OrderhService
 * orderh service 处理类
 * 这个基本类一般继承com.kedacom.security.service.GenericCpsService
 * 如果自己实现转换类，通过重写getXXConveter方法。
 * chenghongtao
 * 2016-11-07 15:30:51
 * HTJ
 * @version 1.0.0
 *
 */
@Service
public class OrderhService extends GenericCpsService<Orderh,OrderhPO,OrderhVO,Long>  {


	@Resource
	private OrderhDao orderhDao;

	@Resource
	private OrderbDao orderbDao;
	
	@Resource
	private OrderhMapper orderhMapper;
	
	@Resource
	private OrderbMapper orderbMapper;

	@Resource
	private ProductDao productDao;

	@Resource
	private HRecvAddressService hRecvAddressService;
	/**
	 * 获取jpa的dao
	 */
	@Override
	protected  OrderhDao getEntityJpaDao ()
	{
		return this.orderhDao;
	}


	/**
	 * 获取 mubatis 的dao
	 */
	@Override
	protected  OrderhMapper getEntityDao ()
	{
		return this.orderhMapper;
	}



	/**
	 * 
	 * saveOrUpdate 保存或者更新
	 * @param orderhVO
	 * @return
	 *OrderhVO
	 * @exception
	 * @since  1.0.0
	 */
	public OrderhVO saveOrUpdate(OrderhVO orderhVO) throws Exception
	{

		if(StringUtil.isNotEmpty(orderhVO.getOrderbData()))
		{
			orderhVO.setOrderbs(JSONUtil.jsonToList(orderhVO.getOrderbData(), OrderbVO.class, new HashMap()));
		}

		if(orderhVO.getId() == null || orderhVO.getId() == 0L)
		{
			return this.add(orderhVO);
		}
		else
		{
			return this.update(orderhVO);
		}

	}
	/**
	 * 重写保存方法，因为牵涉到子表处理
	 */
	@Override
	public OrderhVO add (OrderhVO vo) throws Exception{
		Orderh entity = this.getMoConverter().convert(Orderh.class, vo);

		//TODO 添加其他校验逻辑

		//把子表和主表的关系进行关联。    
//		validate(entity);
		entity = save(entity);

		return getVoConverter().convert(OrderhVO.class, entity); 
	}

	/**
	 * 验证和填充数据
	 * @param entity
	 */
	private void validate(Orderh entity) throws Exception
	{
		Float totalAmount = 0f;
		for(Orderb orderb : entity.getOrderbs())
		{
			orderb.setTotalPrice(new BigDecimal(orderb.getProductNum()).multiply(new BigDecimal(orderb.getUnitPrice())).floatValue());
			orderb.setOrderh(entity);
			totalAmount += orderb.getTotalPrice();
			if(StringUtil.isEmpty(orderb.getExtOrderNo()))
			{
				throw new BusinessException("外部订单号不能为空。");
			}
			if(orderb.getProductId() == null)
			{
				throw new BusinessException("订单没有产品。");
			}
			if(orderb.getRecvId() == null)
			{
				throw new BusinessException("订单送货地址。");
			}
		}
		entity.setTotalAmount(totalAmount)   ;
	}

	/**
	 * 重写update方法。
	 */
	@Override
	public OrderhVO update (OrderhVO vo) throws Exception{
		Orderh entity = this.getMoConverter().convert(Orderh.class, vo);
		//TODO 添加其他校验逻辑
		//把子表和主表的关系进行关联。    
		Orderh dbentity = this.getEntityJpaDao().findOne(vo.getId());
		if(!dbentity.getStatus().equals(Orderh.STATUS_INIT))
		{
			throw new BusinessException("非草稿状态数据不能更新。");
		}
		BeanUtils.copySpecifiedProperties(dbentity,entity , "cusId","cusName","orderDate","orderType","remark");
		
		Float totalAmount = 0f;
		for(Orderb orderb : dbentity.getOrderbs())
		{
			orderb.setTotalPrice(new BigDecimal(orderb.getProductNum()).multiply(new BigDecimal(orderb.getUnitPrice())).floatValue());
			orderb.setOrderh(entity);
			totalAmount += orderb.getTotalPrice();
		}
		dbentity.setTotalAmount(totalAmount)   ;

		dbentity = save(dbentity);

		return getVoConverter().convert(OrderhVO.class, dbentity); 
	}	

	/**
	 * 提交订单
	 * @param id
	 */
	public void submintOrderh(Long id)
	{
		Orderh entity = this.getEntityJpaDao().findOne(id);
		Float totalAmount = 0f;
		for(Orderb orderb : entity.getOrderbs())
		{
			orderb.setTotalPrice(new BigDecimal(orderb.getProductNum()).multiply(new BigDecimal(orderb.getUnitPrice())).floatValue());
			orderb.setOrderh(entity);
			totalAmount += orderb.getTotalPrice();
			
			if(StringUtil.isEmpty(orderb.getExtOrderNo()))
			{
				throw new BusinessException("外部订单号不能为空。");
			}
			if(orderb.getProductId() == null)
			{
				throw new BusinessException("订单没有产品。");
			}
			if(orderb.getRecvId() == null)
			{
				throw new BusinessException("订单送货地址。");
			}
		}
		entity.setTotalAmount(totalAmount)   ;
		entity.setStatus(Orderh.STATUS_SUBMIT);
		this.getEntityJpaDao().save(entity);
	}

	public void cancelOrderh(Long id) throws Exception
	{
		Orderh entity = this.getEntityJpaDao().findOne(id);
		if(!entity.getStatus().equals(Orderh.STATUS_SUBMIT))
			throw new BusinessException("非提交状态不能撤回。");
		entity.setStatus(Orderh.STATUS_INIT);
		this.getEntityJpaDao().save(entity);
	}
	
	
	public List<Orderb> parseKunShanExcel(Long orderHid,String area,MultipartFile file) throws Exception
	{
		XSSFWorkbook rwb = null;
		List<Orderb> orderList = new ArrayList<Orderb>();
		try{
			rwb = new XSSFWorkbook(new ByteArrayInputStream(file.getBytes()));
			XSSFSheet sheet = rwb.getSheetAt(0);
			int number = sheet.getLastRowNum();
			Orderh orderh = new Orderh();
			orderh.setId(orderHid);
			Orderh orderhdb = this.orderhDao.findOne(orderHid);
			XSSFRow row1 = sheet.getRow(0);
			int col = 2;
			while(true)
			{
				if( row1.getCell(col) == null)
				{
					break;
				}
				String productName = row1.getCell(col).getStringCellValue();
				if(StringUtil.isEmpty(productName))
				{
//					throw new BusinessException("产品名称为空");
					break;
				}
				Product p = this.productDao.findByName(productName);
				if(p == null)
				{
					p = this.productDao.findByPName(productName);
				}
				if(p == null)
				{
					throw new BusinessException("产品名称："+productName + " 系统未找到相关产品。");
				}
				String orderNo = ExcelUtil.getCellValue(sheet.getRow(1).getCell(col));
				if(StringUtil.isEmpty(orderNo))
				{
					throw new BusinessException("第："+col + "列("+p.getPName()+")缺失订单号。");
				}
				for(int i =2; i <= number-1; i++)
				{
					try{
						
						XSSFRow row = sheet.getRow(i);
						Double d = row.getCell(col) != null?row.getCell(col).getNumericCellValue():null ;
						if(d == null || d <= 0.0d)
							continue;
						Orderb o = new Orderb();
						String addNo = ExcelUtil.getCellValue(row.getCell(0));
						if(!StringUtil.isNumberic(addNo))
							continue;
						o.setExtOrderNo(orderNo + "-" + addNo);
						o.setProductName(productName);
						o.setProductSpec(p.getSpec());
						o.setUnit(p.getUnit());
						o.setProductNum(d.longValue());
						o.setUnitPrice(p.getSprice());
						o.setProductId(p.getId());
						o.setTotalPrice(new BigDecimal(o.getProductNum()).multiply(new BigDecimal(o.getUnitPrice())).floatValue());
						
						HRecvAddress recv = hRecvAddressService.findByNameAndArea(row.getCell(1).getStringCellValue(),
								area);
						if(recv != null)
						{
							o.setRecvId(recv.getId());
							o.setRecvName(recv.getCusName());
							o.setRecvAdd(recv.getCusAddress());
							o.setRecvContactor(recv.getContractor());
							o.setRecvTel(recv.getTelphone() + "/" + recv.getCellphone());
						} 
						o.setOrderh(orderh);
						o.setIsPlanSend(0);
						o.setIsSigned(0);
						orderList.add(o);
					}catch(Exception ex)
					{
						throw new BusinessException( "第"+ (i+1) + "行处理错误："+ ExceptionUtils.getRootCauseMessage(ex),ex);
					}
				}
				
				
				col ++;
			}
			
			
			
			
			
			
			
		}finally{
			if(rwb != null)
				rwb.close();
		}
		this.orderbDao.save(orderList);
		return orderList;
	}
	
	public List<Orderb> parseFromExcel(Long orderHid,MultipartFile file) throws Exception
	{

		XSSFWorkbook rwb = null;
		List<Orderb> orderList = new ArrayList<Orderb>();
		try{
			rwb = new XSSFWorkbook(new ByteArrayInputStream(file.getBytes()));
			XSSFSheet sheet = rwb.getSheetAt(0);
			int number = sheet.getLastRowNum();
			Orderh orderh = new Orderh();
			orderh.setId(orderHid);
			for(int i =1; i <= number; i++)
			{
				try{
					Orderb o = new Orderb();
					XSSFRow row = sheet.getRow(i);

					o.setExtOrderNo(ExcelUtil.getCellValue(row.getCell(0)));

					String orderProductName = ExcelUtil.getCellValue(row.getCell(1));
					if(StringUtil.isEmpty(orderProductName))
						continue;
					Product p = this.productDao.findByName(orderProductName);
					if(p == null)
					{
						p = this.productDao.findByPName(orderProductName);
					}
					if(p == null)
					{
						throw new BusinessException("产品名称："+orderProductName + " 系统未找到相关产品。");
					}
					o.setProductId(p.getId());
					o.setProductName(orderProductName);
					o.setProductSpec(ExcelUtil.getCellValue(row.getCell(2)));
					o.setUnit(ExcelUtil.getCellValue(row.getCell(3)));
					Double d = row.getCell(4).getNumericCellValue() ;
					o.setProductNum(d.longValue());
					d = row.getCell(5).getNumericCellValue();
					o.setUnitPrice(d.floatValue());
					o.setTotalPrice(new BigDecimal(o.getProductNum()).multiply(new BigDecimal(o.getUnitPrice())).floatValue());
					String rname = ExcelUtil.getCellValue(row.getCell(7));
					String raddress = ExcelUtil.getCellValue(row.getCell(8));
					String tel = ExcelUtil.getCellValue(row.getCell(9));

					HRecvAddress recv = hRecvAddressService.findByName(rname);
					if(recv != null)
					{
						o.setRecvId(recv.getId());
						o.setRecvName(recv.getCusName());
						o.setRecvAdd(recv.getCusAddress());
						o.setRecvContactor(recv.getContractor());
						o.setRecvTel(recv.getTelphone() + "/" + recv.getCellphone());
					} 
					o.setOrderh(orderh);
					o.setIsPlanSend(0);
					o.setIsSigned(0);
					orderList.add(o);
				}catch(Exception ex)
				{
					throw new BusinessException( "第"+ (i+1) + "行处理错误："+ ExceptionUtils.getRootCauseMessage(ex),ex);
				}
			}


		}finally{
			if(rwb != null)
				rwb.close();
		}
		this.orderbDao.save(orderList);
		return orderList;
	}


	@Override
	public void delById(Long id) throws Exception {
		Orderh order = this.getEntityJpaDao().findOne(id);
		if(order == null)
			return;
		if(!order.getStatus().equals(Orderh.STATUS_INIT))
			throw new BusinessException("订单"+order.getCusName()+"已经提交确认，不能删除。");
		//删除子表
		this.orderbDao.delByOrderH(id);
		this.orderhDao.delete(id);
	}


	@Override
	public void delByIds(Long[] ids) throws Exception {
		for(Long id : ids)
		{
			delById(id);
		}
	}
	
	
	public void export(String ids,OutputStream os) throws Exception
	{
		String[] idArr = ids.split(",");
		
		String file = ApplicationUtil.getAppAbsolutePath() + File.separator + "WEB-INF" + File.separator 
				+ "tpl" + File.separator + "order_tpl.xls";
		
		HSSFWorkbook workbook = null;
		FileInputStream fis = null;
		try{
			fis = new FileInputStream(file);
			workbook = new HSSFWorkbook(fis);
			List<OrderbPO> poList = this.orderbMapper.sumOrderInfo(ids);
			Double totalPrice = 0d;
			HSSFSheet sheet = workbook.getSheetAt(1);
			for(int i =1; i < poList.size(); i++)
			{
				HSSFRow row =  sheet.createRow(i+1);
				ExcelUtil.copyRow(workbook, sheet.getRow(1), row, false);
			}
			
			int j = 1;
			for(OrderbPO po : poList)
			{
				HSSFRow row = null;
				row = sheet.getRow(j);
				row.getCell(0).setCellValue(po.getProductName());
				row.getCell(1).setCellValue(po.getVouchName());
				row.getCell(2).setCellValue(po.getUnit());
				row.getCell(3).setCellValue(po.getProductNum());
				row.getCell(4).setCellValue(po.getUnitPrice());
				row.getCell(5).setCellValue(po.getTotalPrice());
				totalPrice += po.getTotalPrice();
				j++;
			}
			HSSFRow row =  sheet.createRow(j);
			ExcelUtil.copyRow(workbook, sheet.getRow(1), row, false);
			sheet.addMergedRegion(new CellRangeAddress(j, j, 0, 4));
			row.getCell(0).setCellValue("汇总:");
			row.getCell(5).setCellValue(totalPrice);
			
			int t =0;
			for(String id : idArr)
			{
				sheet = workbook.cloneSheet(0);;
				List<Orderb> orderbList = this.orderbDao.findByOrderh(new Long(id));
				int size = orderbList.size();
				for(int i =1; i < size; i++)
				{
					row =  sheet.createRow(i+1);
					ExcelUtil.copyRow(workbook, sheet.getRow(1), row, false);
				}
				j =1;
				for(Orderb orderb : orderbList)
				{
					row = null;
					row = sheet.getRow(j);
					row.getCell(0).setCellValue(orderb.getExtOrderNo());
					row.getCell(1).setCellValue(orderb.getProductName());
					row.getCell(2).setCellValue(orderb.getProductSpec());
					row.getCell(3).setCellValue(orderb.getUnit());
					row.getCell(4).setCellValue(orderb.getProductNum());
					row.getCell(5).setCellValue(orderb.getUnitPrice());
					row.getCell(6).setCellValue(orderb.getTotalPrice());
					row.getCell(7).setCellValue(orderb.getRecvName());
					row.getCell(8).setCellValue(orderb.getRecvAdd());
					row.getCell(9).setCellValue(orderb.getRecvTel());
					j++;
				}
				if(t == 0)
				{
					//苏州惠特佳电子商务有限公司	联行号:102305018050	中国工商银行股份有限公司苏州竹园路分理处	1102180509000038514
					j++;
					row =  sheet.createRow(j);
					ExcelUtil.copyRow(workbook, sheet.getRow(1), row, false);
					sheet.addMergedRegion(new CellRangeAddress(j, j, 0, 1));
					row.getCell(0).setCellValue("公司名称");
					sheet.addMergedRegion(new CellRangeAddress(j, j, 2, 9));
					row.getCell(2).setCellValue("苏州惠特佳电子商务有限公司");
					j++;
					
					row =  sheet.createRow(j);
					ExcelUtil.copyRow(workbook, sheet.getRow(1), row, false);
					sheet.addMergedRegion(new CellRangeAddress(j, j, 0, 1));
					row.getCell(0).setCellValue("联行号");
					sheet.addMergedRegion(new CellRangeAddress(j, j, 2, 9));
					row.getCell(2).setCellValue("102305018050");
					j++;
					
					row =  sheet.createRow(j);
					ExcelUtil.copyRow(workbook, sheet.getRow(1), row, false);
					sheet.addMergedRegion(new CellRangeAddress(j, j, 0, 1));
					row.getCell(0).setCellValue("开户行");
					sheet.addMergedRegion(new CellRangeAddress(j, j, 2, 9));
					row.getCell(2).setCellValue("中国工商银行股份有限公司苏州竹园路分理处");
					j++;

					row =  sheet.createRow(j);
					ExcelUtil.copyRow(workbook, sheet.getRow(1), row, false);
					sheet.addMergedRegion(new CellRangeAddress(j, j, 0, 1));
					row.getCell(0).setCellValue("账号");
					sheet.addMergedRegion(new CellRangeAddress(j, j, 2, 9));
					row.getCell(2).setCellValue("1102180509000038514");
					j++;
					
					 
				}
				
				t++;
			}
			workbook.removeSheetAt(0);
			workbook.write(os);
			
		}finally{
			if(workbook != null)
				workbook.close();
			if(fis != null)
				fis.close();
//			if(fos != null)
//			{
//				fos.flush();
//				fos.close();
//			}
			
		}
		
		
		
		
		
		
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
}
